using System;
using System.Collections.Generic;
using System.Data;
using System.Threading;
using System.Threading.Tasks;
using System.IO;
using System.Linq.Expressions;
using System.Reflection;
using System.Runtime.Serialization;
using System.Text;
using System.Xml.Linq;
using MySql.Data.MySqlClient;


namespace QDCommon.My
{

    public class MySqCommandHelper
    {
        public string ConnectionStr { get; set; }

        public MySqCommandHelper(string connection)
        {
            ConnectionStr = connection;
        }

        public async Task SqlAction(Func<MySqlConnection, Task> action)
        {
            using (MySqlConnection conn = new MySqlConnection(ConnectionStr))
            {
                await conn.OpenAsync();
                await action(conn);
                await conn.CloseAsync();
            }
        }

        public async Task SqlActionWithTransform(Func<MySqlConnection,MySqlTransaction, Task> action, IsolationLevel leval)
        {
            using (MySqlConnection conn = new MySqlConnection(ConnectionStr))
            {
                await conn.OpenAsync();
                var transfrom = await conn.BeginTransactionAsync(leval);
                try{
                    await action(conn, transfrom);
                }
                catch(Exception e)
                {
                    transfrom.Rollback();
                    throw e;
                }

                await conn.CloseAsync();
            }
        }

        public async Task<List<T>> ExcuteReader<T>(string sql) where T:new()
        {
            List<T> result = null;
            await SqlAction(async (connection) => {
                 using (MySqlCommand command = new MySqlCommand(sql, connection))
                    {
                        using (var reader = await command.ExecuteReaderAsync())
                        {
                            result = MysqlDataReaderHelper.ConvertReader<T>(reader as MySqlDataReader);
                            reader.Close();
                        }
                    }
            });
            return result;
        }
        public async Task<List<T>> ExcuteReader<T>(string sql,MySqlParameter[] paras) where T : new()
        {
            List<T> result = null;
            await SqlAction(async (connection) => {
                using (MySqlCommand command = new MySqlCommand(sql, connection))
                {
                    command.Parameters.AddRange(paras);
                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        result = MysqlDataReaderHelper.ConvertReader<T>(reader as MySqlDataReader);
                        reader.Close();
                    }
                }
            });
            return result;
        }
        public async Task<int> ExecuteUpdate(string sql)
        {
            int count = 0;
            await SqlAction(async (connection) => {
               

                using (MySqlCommand command = new MySqlCommand(sql, connection))
                {
                    var updateCount = await command.ExecuteNonQueryAsync();
                    count = updateCount;
                    
                }
            });
            return count;
        }
        public async Task<int> ExecuteUpdate(string sql,MySqlParameter[] paras)
        {
            int count = 0;
            await SqlAction(async (connection) => {
                using (MySqlCommand command = new MySqlCommand(sql, connection))
                {
                    command.Parameters.Add(paras);
                    var updateCount = await command.ExecuteNonQueryAsync();
                    count = updateCount;

                }
            });
            return count;
        }

        public async Task<T> ExecuteScalar<T>(string sql) where T:struct
        {
            T result = default(T);
            await SqlAction(async (connection) => {
                
                using (MySqlCommand command = new MySqlCommand(sql, connection))
                {
                    var sqlResult = await command.ExecuteScalarAsync();
                    result = (T)sqlResult;

                }
            });
            return result;
        }
        public async Task<T> ExecuteScalar<T>(string sql, MySqlParameter[] paras) where T : struct
        {
            T result = default(T);
            await SqlAction(async (connection) => {

                using (MySqlCommand command = new MySqlCommand(sql, connection))
                {
                    command.Parameters.AddRange(paras);
                    var sqlResult = await command.ExecuteScalarAsync();
                    result = (T)sqlResult;

                }
            });
            return result;
        }
    }
}
